﻿using CBS.Common;
using CBS.DB.CBSEntity;
using CBS.DB;
using CBS.Model.ManageModel;
using CBS.Model.UIModel;
using CBS.Model.ViewModel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using CBS.Common.Office;
using System.Data;
using NPOI.HPSF;
using System.Data.Common;
using MySqlConnector;
using System.Collections;

namespace CBS.APP
{
    public class CoursePlanApp
    {
        public static bool CreateCoursePlan(CoursePlanViewModel coursePlan)
        {
            using CBSContext cbs = new();
            cbs.cbs_course_plan.Add(new cbs_course_plan
            {
                CoursePlanName = coursePlan.CoursePlanName,
                SemesterId = coursePlan.SemesterId,
                ClassId = coursePlan.ClassId,
                CourseId = coursePlan.CourseId,
                TeacherId = coursePlan.TeacherId,
                ClassTimeLong = coursePlan.ClassTimeLong,
                CoursePrice = coursePlan.CoursePrice,
                CourseCount = coursePlan.CourseCount,
                GradeId = coursePlan.GradeId,
                PlanNum = coursePlan.PlanNum,
                Memo = coursePlan.Memo
            });

            return cbs.SaveChanges() > 0;
        }

        public static bool UpdateCoursePlan(int coursePlanId, CoursePlanViewModel coursePlan)
        {
            using CBSContext cbs = new();

            var data = cbs.cbs_course_plan.FirstOrDefault(t => t.CoursePlanId == coursePlanId);
            if (data == null)
            {
                return false;
            }

            //data.CoursePlanName = coursePlan.CoursePlanName;
            //data.SemesterId = coursePlan.SemesterId;
            //data.ClassId = coursePlan.ClassId;
            //data.CourseId = coursePlan.CourseId;
            //data.TeacherId = coursePlan.TeacherId;
            data.ClassTimeLong = coursePlan.ClassTimeLong;
            data.CoursePrice = coursePlan.CoursePrice;
            data.CourseCount = coursePlan.CourseCount;
            //data.GradeId = coursePlan.GradeId;
            data.PlanNum = coursePlan.PlanNum;
            data.Memo = coursePlan.Memo;

            cbs.cbs_course_plan.Update(data);
            return cbs.SaveChanges() > 0;
        }

        public static List<CoursePlanViewModel> GetList(Pagination page, int semesterId, int gradeId, int classId, int courseId, string? keyword)
        {
            string sql = @"select a.*,b.SemesterName,c.TeacherName,d.GradeName,e.ClassName,f.CourseName
from cbs_course_plan a
	left join cbs_semester b on a.SemesterId = b.SemesterId
	left join cbs_teacher c on a.TeacherId = c.TeacherId
	left join cbs_grade d on a.GradeId = d.GradeId
	Left join cbs_class e on a.ClassId = e.ClassId
	left join cbs_course f on a.CourseId = f.CourseId
where a.SemesterId = @semesterId
and (@gradeId = 0 or a.GradeId = @gradeId)
and (@classId = 0 or a.ClassId = @classId)
and (@courseId = 0 or a.CourseId = @courseId)
and (@keyword = '' or a.CoursePlanName like @keyword)";

            MySqlParameter[] parameters =
            {
                new MySqlParameter("@semesterId",semesterId),
                new MySqlParameter("@gradeId",gradeId),
                new MySqlParameter("@classId",classId),
                new MySqlParameter("@courseId",courseId),
                new MySqlParameter("@keyword",$"%{keyword}%"),
            };

            return DBHelper.FindListOrderBy<CoursePlanViewModel>(sql, page, FromPositionInSql.First, parameters);

        }

        public static List<CourseViewModel> GetSemesterSetCourseName(int semesterId)
        {
            using CBSContext cbs = new();
            //return (from semester in cbs.cbs_semester
            //        join classPlan in cbs.cbs_class on semester.SemesterId equals classPlan.SemesterId
            //        where semester.SemesterId == semesterId
            //        select classPlan.ClassPlanName).ToList();

            return (from coursePlan in cbs.cbs_course_plan
                    join course in cbs.cbs_course on coursePlan.CourseId equals course.CourseId
                    where coursePlan.SemesterId == semesterId
                    select new CourseViewModel() { CourseId = coursePlan.CoursePlanId, CourseName = course.CourseName }).ToList();
        }

        public static List<CoursePlanSelectModel> GetSelectList(int semesterId)
        {
            using CBSContext cbs = new();
            var list = (from s in cbs.cbs_course_plan
                        where s.SemesterId == semesterId
                        orderby s.CoursePlanId descending
                        select new CoursePlanSelectModel
                        {
                            CoursePlanId = s.CoursePlanId,
                            CoursePlanName = s.CoursePlanName,
                        }).ToList();

            return list;
        }

        public static List<CoursePlanSelectModel> GetFullDataSelectList(int semesterId)
        {
            using CBSContext cbs = new();
            var list = (from s in cbs.cbs_course_plan
                        join grade in cbs.cbs_grade on s.GradeId equals grade.GradeId
                        join clas in cbs.cbs_class on s.ClassId equals clas.ClassId
                        join cour in cbs.cbs_course on s.CourseId equals cour.CourseId
                        where s.SemesterId == semesterId
                        orderby s.CoursePlanId descending
                        select new CoursePlanSelectModel
                        {
                            CoursePlanId = s.CoursePlanId,
                            CoursePlanName = s.CoursePlanName + "-" + grade.GradeName + "-" + clas.ClassName + "-" + cour.CourseName,
                        }).ToList();

            return list;
        }

        public static bool DeleteCoursePlan(int coursePlanId)
        {
            using CBSContext cbs = new();

            var coursePlan = cbs.cbs_course_plan.FirstOrDefault(t => t.CoursePlanId == coursePlanId);
            if (coursePlan == null)
            {
                return false;
            }

            if (cbs.cbs_lesson.Any(t => t.CoursePlanId == coursePlanId)) { return false; }

            if (cbs.cbs_student_signup.Any(t => t.CoursePlanId == coursePlanId)) { return false; }

            cbs.cbs_course_plan.Remove(coursePlan);

            return cbs.SaveChanges() > 0;
        }

        public static bool IsCoursePlanUsed(int coursePlanId)
        {
            using CBSContext cbs = new();
            return cbs.cbs_student_signup.Any(t => t.CoursePlanId == coursePlanId)
                || cbs.cbs_lesson.Any(t => t.CoursePlanId == coursePlanId);
        }

        public static bool IsCoursePlanUsed(int semesterId, string? key)
        {
            using CBSContext cbs = new();
            var list = cbs.cbs_course_plan.Where(t => t.SemesterId == semesterId).Select(t => t.CoursePlanId).ToList();
            return cbs.cbs_student_signup.Any(t => list.Contains(t.CoursePlanId));
        }

        public static bool BatchDeleteCoursePlan(int semesterId, string coursePlanName)
        {
            using CBSContext cbs = new();

            var coursePlans = cbs.cbs_course_plan.Where(t => t.SemesterId == semesterId && (coursePlanName == "" || t.CoursePlanName.Contains(coursePlanName))).ToList();
            if (coursePlans.Count == 0) { return false; }

            int[] coursePlanIds = coursePlans.Select(t => t.CoursePlanId).ToArray();

            if (cbs.cbs_lesson.Any(t => coursePlanIds.Contains(t.CoursePlanId)))
            {
                return false;
            }

            if (cbs.cbs_student_signup.Any(t => coursePlanIds.Contains(t.CoursePlanId)))
            {
                return false;
            }

            cbs.cbs_course_plan.RemoveRange();
            return cbs.SaveChanges() > 0;
        }

        public static CoursePlanViewModel GetForm(int coursePlanId)
        {
            using CBSContext cbs = new();
            var entity = cbs.cbs_course_plan.FirstOrDefault(t => t.CoursePlanId == coursePlanId);

            return PropertyHelper.Assignment<CoursePlanViewModel, cbs_course_plan>(entity);
        }

        public static bool ImportCoursePlan(string filePath, out string msg)
        {
            msg = "";

            //验证导入格式
            DataTable dtFirstRow = new DataTable();
            var filePostfixName = filePath.Substring(filePath.LastIndexOf('.') + 1);
            try
            {
                dtFirstRow = ExcelHelper.GetFirstRowData(filePath, filePostfixName);
            }
            catch (Exception ex)
            {
                if (ex.Message.IndexOf("表头不允许为空") > -1)
                {
                    msg = ex.Message;
                }
                msg = "文档不符合要求，请检查。";
            }


            return false;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="examId"></param>
        /// <returns></returns>
        public static string ValidExcel(DataTable dt)
        {

            string[] strStandardNames = new string[5];

            for (int i = 0; i < dt.Columns.Count; i++)
            {
                dt.Columns[i].ColumnName = dt.Columns[i].ColumnName.Trim();
            }
            //要求导入设置每一列都包含在Excel中
            for (int i = 0; i < strStandardNames.Length; i++)
            {
                if (!dt.Columns.Contains(strStandardNames[i]))
                {
                    return "导入的Excel表头内容有错误！第一行表头必须包含:" + string.Join(",", strStandardNames) + "。请使用正确的模板！";
                }
            }

            return "";
        }
    }
}
